﻿using System;
using System.Data;
using System.Collections.Generic;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class LabourRelationDAL : ILabourRelationDAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(int? intUploadSeq, string strFileName, string strFilePath, string strRemark, int? intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@upload_category_seq", intUploadSeq);
                dbManager.AddParameters(1, "@file_name", strFileName);
                dbManager.AddParameters(2, "@file_path", strFilePath);
                dbManager.AddParameters(3, "@remark", strRemark);
                dbManager.AddParameters(4, "@created_by", intCreateBy);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);

                strSQL = "INSERT INTO hrm_labour_upload_file"
                         + " (upload_category_seq, "
                         + " file_name,"
                         + " file_path,"
                         + " remark,"
                         + " created_when,"
                         + " created_by, record_status)"
                         + " VALUES"
                         + "(@upload_category_seq,"
                         + " @file_name,"
                         + " @file_path,"
                         + " @remark,"
                         + " GETDATE(),"
                         + " @created_by, @record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int? intFileSeq, int? intUploadSeq, string strFileName, string strFilePath, string strRemark, int? intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@upload_file_seq", intFileSeq);
                dbManager.AddParameters(1, "@upload_category_seq", intUploadSeq);
                dbManager.AddParameters(2, "@file_name", strFileName);
                dbManager.AddParameters(3, "@file_path", strFilePath);
                dbManager.AddParameters(4, "@remark", strRemark);
                dbManager.AddParameters(5, "@updated_by", intCreateBy);
                dbManager.AddParameters(6, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " UPDATE hrm_labour_upload_file "
                         + " SET upload_category_seq = @upload_category_seq, "
                         + " file_name = @file_name,"
                         + " file_path = @file_path,"
                         + " remark = @remark,"
                         + " updated_when = GETDATE(), "
                         + " updated_by = @updated_by"
                         + " WHERE upload_file_seq = @upload_file_seq AND record_status not like @status ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int? intFileSeq, string strFileTrash, int? intCreateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@upload_file_seq", intFileSeq);
                dbManager.AddParameters(1, "@file_path", strFileTrash);
                dbManager.AddParameters(2, "@updated_by", intCreateBy);
                dbManager.AddParameters(3, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " UPDATE hrm_labour_upload_file SET"
                        + " record_status = @status,"
                        + " file_path = @file_path,"
                        + " updated_by = @updated_by,"
                        + " updated_when = GETDATE()"
                        + " WHERE upload_file_seq = @upload_file_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Search(int? intFileSeq, int? intUploadSeq, string strFileName, string strFilePath, string strRemark)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@upload_category_seq", intFileSeq);
                dbManager.AddParameters(1, "@upload_file_seq", intUploadSeq);
                dbManager.AddParameters(2, "@file_name", strFileName);
                dbManager.AddParameters(3, "@file_path", strFilePath);
                dbManager.AddParameters(4, "@remark", strRemark);
                dbManager.AddParameters(5, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT a.upload_file_seq, b.upload_category_seq, a.file_name, a.file_path, a.remark , b.category_name"
                        +" FROM hrm_labour_upload_file a"
                        +" INNER JOIN hrm_labour_upload_category b ON a.upload_category_seq = b.upload_category_seq"
                        + " WHERE a.record_status not like @status ORDER BY upload_file_seq ASC";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool CheckName(string StrCategoryName)
        {
            string strSQL = "";
            
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@name", StrCategoryName);

                strSQL = " SELECT * FROM hrm_labour_upload_category"
                         + " WHERE category_name = @name";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb.Rows.Count > 0;
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadCategory()
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT upload_category_seq, category_name FROM hrm_labour_upload_category "
                         +" WHERE record_status not like @status";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool CheckValueSubmit(int? intFileSeq)
        {
            string strSQL;

            bool result = true;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@upload_file_seq", intFileSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT upload_file_seq, upload_category_seq, file_name, remark, file_path"
                         + " FROM hrm_labour_upload_file"
                         + " WHERE record_status = @status ";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return result;
        }
    }
}